package Tra

import org.apache.spark.sql.SparkSession

import java.util.Properties


object t1 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().getOrCreate()

    val url = "jdbc:clickhouse://bigdata1:8123/hhd"
    val prop = new Properties()
    prop.put("user","default")
    prop.put("password","123456")
    prop.put("driver","ru.yandex.clickhouse.ClickHouseDriver")

    spark.sql(
      """
        |from hhd.tra t join hhd.speedMax s on t.monitorId = s.monitor_id
        |   select
        |   distinct
        |       car,
        |       count(car) counts,
        |       date_format(actionId,'yyyy-MM-dd') actionTime
        |   where t.speed >= s.speed_limit
        |   group by car,date_format(actionId,'yyyy-MM-dd')
        |""".stripMargin)
      .write.mode("append")
      .jdbc(url,"speedThe3Count",prop)
  }
}
